<?php
//include_once 'lock.php';
include_once 'fns_db.php';
include_once 'fns.php';


/*
Эксперементальная функция для формирования запроса
function buildStatement( $table, array $fields, array $conditions=null ) {
    if ( ! is_null( $conditions ) ) {
        $query  = "UPDATE `{$table}` SET ";
        $re=array();
        foreach ($fields as $k => $v) {
            if (is_null($v)) { $re[]="`$k` = NULL"; continue; }
            if (is_string($v)) { $re[]="`$k` = '$v'"; continue; }
            $re[]="`$k` = $v";
        }
        $query.=implode(", ", $re);
        $cond = array();
        $query .= " WHERE ";
        foreach ( $conditions as $key=>$val ) {
            if (is_null($val)) { $cond[]="`$key` = NULL"; continue; }
            if (is_string($val)) { $cond[]="`$key` = '$val'"; continue; }
            $cond[]="`$key` = $val";
        }
        $query .= implode( " AND ", $cond );
    } else {
        $query  = "INSERT INTO `{$table}` (";
        $re=array();
        foreach ($fields as $k => $v) {
            $re[]="`$k`";
        }
        $query.=implode(", ", $re);            
        $query .= ") VALUES (";
        foreach ( $fields as $name => $value ) {
            if (is_null($value)) { $qs[]="NULL"; continue; }
            if (is_string($value)) { $qs[]="'$value'"; continue; }
            $qs[]="$value";
        }
        $query .= implode( ",", $qs );
        $query .= ")";
    }
    return $query;
}
	
$table="emp";	
$fields['id']=1;
$fields['name']='Петров И.И.';
$fields['id_dep']=1;
$fields['price']=1.99;
//$cond['id']=1;
//$cond['id_dep']=2;
$cond=NULL;
$query=buildStatement($table,$fields,$cond);
echo $query;

 */



function edit_element_select($id,$old,$new,$funct_del,$funct_add,$type) {
    if (!is_array($new)) $new=array();
    if (!is_array($old)) $old=array();
    $diff=array_diff($old, $new);
    $diff_size=count($diff);
    $i=0;
    $del=array();
    foreach ($diff as $key => $value) {
        $del[$i]=$value;
        $i++;
        if ($type=='tel') call_user_func($funct_del, $id, $value);
        if ($type=='emp') call_user_func($funct_del, $value, $id);
    }
    //call_user_func($funct_del, $id, $del);
    //del_user_group_db($id_user, $del);
    $i=0;
    $diff=array_diff($new, $old);
    $diff_size=count($diff);
    $add=array();
    foreach ($diff as $key => $value) {
        $add[$i]=$value;
        $i++;
        if ($type=='tel') call_user_func($funct_add, $id, $value);
        if ($type=='emp') call_user_func($funct_add, $value, $id);
    }  
    //call_user_func($funct_add, $id, $add);
    //add_user_group_db($id_user, $add);
}

function del_tel_emp_db($id_tel,$id_emp) {
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="delete from `tel_emp` where id_tel=".$id_tel." and id_emp=".$id_emp;
    $result=$conn->query($query);
    if (!$result) return FALSE;
    return TRUE;
}

function add_tel_emp_db($id_tel,$id_emp) {
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="insert into `tel_emp` value (".$id_emp.",".$id_tel.")";
    $result=$conn->query($query);
    if (!$result) return FALSE;
    return TRUE;
}



function add_column_table(&$array,$action,$param=NULL) {
    $array_size=count($array);
    $array_size1=count($array[0]);
    for ($i=0;$i<$array_size;$i++) {
        $array[$i][$array_size1]=create_url('admin.php?action='.$action.'&id='.$array[$i][0], 'ред.');
    }
}


function check_select($check_array,$tbl_name,$err) {
    if ($check_array[0]==0) return TRUE;
    $count_list=count($check_array);
    $in_text='(';
    for ($i=0;$i<$count_list;$i++) {
        if ($i==$count_list-1) $in_text.=$check_array[$i];
        else $in_text.=$check_array[$i].',';
    }
    $in_text.=')';
    $conn=db_connect();
    $query="select count(*) from `".$tbl_name."` where id in ".$in_text;
    $result=$conn->query($query);
    if (!$result) return FALSE;

    $row=$result->fetch_assoc();
    if (!$row) { $err.='неверная опциея выбора'; return FALSE; }
    foreach ($row as $key => $value) {
        $result_size=$value;
    } 
    //echo '$value='.$result_size.'<br />';
    if ($result_size>0) return TRUE;
    $err.='нет совпадений'; 
    return FALSE; 
}

function valid_emp(&$name,&$comment,&$err){
    $err='';
    if (empty($name)) $err.=ERR_EMPNAME_EMPTY;
    if (!empty($err)) return FALSE;
    $name=trim($name);
    $comment=trim($comment);
    if (mb_strlen($name,'utf8')>100 || mb_strlen($name,'utf8')<3) $err.=ERR_LEN_EMPNAME;
    if (mb_strlen($comment,'utf8')>100) $err.=ERR_LEN_GROUPCOMMENT;
    if (!empty($err)) return FALSE;
    
    return TRUE;
}

function valid_org(&$name,&$err){
    $err='';
    if (empty($name)) $err.=ERR_ORGNAME_EMPTY;
    if (!empty($err)) return FALSE;
    $name=trim($name);
    if (mb_strlen($name,'utf8')>150 || mb_strlen($name,'utf8')<3) $err.=ERR_LEN_ORGNAME;
    if (!empty($err)) return FALSE;
    return TRUE;
}

function valid_post(&$name,&$err) {
    $err='';
    if (empty($name)) $err.=ERR_POSTNAME_EMPTY;
    if (!empty($err)) return FALSE;
    $name=trim($name);
    if (mb_strlen($name,'utf8')>100 || mb_strlen($name,'utf8')<2) $err.=ERR_LEN_POSTNAME;
    if (!empty($err)) return FALSE;
    return TRUE;
}

function valid_dep(&$name,&$err) {
    $err='';
    if (empty($name)) $err.=ERR_DEPNAME_EMPTY;
    if (!empty($err)) return FALSE;
    $name=trim($name);
    if (mb_strlen($name,'utf8')>100 || mb_strlen($name,'utf8')<1) $err.=ERR_LEN_DEPNAME;
    if (!empty($err)) return FALSE;
    return TRUE;   
}

function valid_tel(&$number,&$typetel,&$err) {
    $err='';
    if (empty($number)) $err.=ERR_TELNAME_EMPTY;
    if (empty($typetel)) $err.=ERR_TELTYPE_EMPTY;
    if (!empty($err)) return FALSE;
    if (mb_strlen($number,'utf8')>100 || mb_strlen($number,'utf8')<1) $err.=ERR_LEN_TELNAME;
    //if (($typetel!=0) || ($typetel!=1) || ($typetel!=2) || ($typetel!=3)) $err.=ERR_TELTYPE;
    if (!empty($err)) return FALSE;
    return TRUE;   
}


function add_emp(&$name,&$dep,&$comment,&$post,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    if ($dep[0]==0) $query="insert into `emp` (`id`,`name`,`comment`,`id_dep`,`name_translit`,`id_post`)
        value(NULL,'".$conn->real_escape_string(strip_tags($name))."','".$conn->real_escape_string(strip_tags($comment))."',NULL,'".fio2shortfio(strtrans($conn->real_escape_string(strip_tags($name))))."',".$post[0].")";
    else
    $query="insert into `emp` (`id`,`name`,`comment`,`id_dep`,`name_translit`,`id_post`)
        value(NULL,'".$conn->real_escape_string(strip_tags($name))."','".$conn->real_escape_string(strip_tags($comment))."',".$conn->real_escape_string(strip_tags($dep[0])).",'".fio2shortfio(strtrans($conn->real_escape_string(strip_tags($name))))."',".$post[0].")";
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;
}

function add_org(&$name,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="insert into `org` (`id`,`name`)
        value(NULL,'".$conn->real_escape_string(strip_tags($name))."')";
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;    
}

function add_post(&$name,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="insert into `post` (`id`,`name`)
        value(NULL,'".$conn->real_escape_string(strip_tags($name))."')";
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;    
}

function add_dep(&$name,&$org,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    if (!empty($org))
    $query="insert into `dep` (`id`,`name`,`id_org`)
        value(NULL,'".$conn->real_escape_string(strip_tags($name))."',".$conn->real_escape_string(strip_tags($org)).")";
    else $query="insert into `dep` (`id`,`name`,`id_org`)
        value(NULL,'".$conn->real_escape_string(strip_tags($name))."',NULL)";
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;    
}

function add_tel(&$number,&$typetel,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="insert into `tel` (`id`,`number`,`typenumber`)
        value(NULL,'".$conn->real_escape_string(strip_tags($number))."',".$conn->real_escape_string(strip_tags($typetel[0])).")";
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;    
}

function upd_tel(&$id,&$number,&$typetel,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="update `tel` set `number`='".$conn->real_escape_string(strip_tags($number))."', `typenumber`='".$conn->real_escape_string(strip_tags($typetel[0]))."' where id=".$id;
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;       
}


function upd_org(&$id,&$name,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="update `org` set `name`='".$conn->real_escape_string(strip_tags($name))."' where id=".$id;
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;       
}

function upd_post(&$id,&$name,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $query="update `post` set `name`='".$conn->real_escape_string(strip_tags($name))."' where id=".$id;
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;       
}

function upd_dep(&$id,&$name,&$org,&$err){
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    if ($org[0]==0) $query="update `dep` set `name`='".$conn->real_escape_string(strip_tags($name))."', `id_org`=NULL where id=".$id;
    else $query="update `dep` set `name`='".$conn->real_escape_string(strip_tags($name))."', `id_org`=".$conn->real_escape_string(strip_tags($org[0]))." where id=".$id;
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;       
}

function upd_emp(&$id,&$name,&$dep,&$comment,&$post,&$err) {
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $len=mb_strlen($comment);
    if ($comment!=0){
        $query="update `emp` set `name`='".$conn->real_escape_string(strip_tags($name))."','".$conn->real_escape_string(strip_tags($comment))."', `id_dep`=".$conn->real_escape_string(strip_tags($dep[0])).", `name_translit`='".fio2shortfio(strtrans($conn->real_escape_string(strip_tags($name))))."', `id_post`=".$post[0]." where id=".$id;
    } else {
        $query="update `emp` set `name`='".$conn->real_escape_string(strip_tags($name))."',`id_dep`=".$conn->real_escape_string(strip_tags($dep[0])).", `name_translit`='".fio2shortfio(strtrans($conn->real_escape_string(strip_tags($name))))."', `id_post`=".$post[0]."  where id=".$id;
    }
    
    $result=$conn->query($query);
    if (!$result) $err.=ERR_SAVE_USER_DB.mysqli_connect_error();
    else return TRUE;
    return FALSE;           
}


function check_count_dep() {
    $conn=db_connect();
    $query="select count(id) from `dep`";
    $result=$conn->query($query);
    if (!$result) return FALSE;
    $row=$result->fetch_row();
    if ($row[0]<1) return FALSE;
    return TRUE;
}

function check_count_org(){
    $conn=db_connect();
    $query="select count(id) from `org`";
    $result=$conn->query($query);
    if (!$result) return FALSE;
    $row=$result->fetch_row();
    if ($row[0]<1) return FALSE;
    return TRUE;    
}

function store_emp(){
    $action=$_POST['action'];
    if (isset($_POST['name']) && !empty($_POST['name'])) {
        $name=$_POST['name'];
        $id=$_POST['id'];
        $comment=$_POST['comment'];
        $dep=$_POST['dep'];
        $post=$_POST['post'];
        if (isset($_REQUEST['telemp'])) {
            $telemp=$_REQUEST['telemp'];
        } else $telemp=NULL;            
        $err='';
        if (valid_emp($name,$comment,$err) && check_select($dep,'dep',$err)){
            switch ($action) {
                case ADD_ALL:
                case ADD_EMP:
                    if (add_emp($name,$dep, $comment,$post, $err)){
                        if (!is_null($telemp)) {
                            $count=count($telemp);
                            $conn=db_connect();
                            $insert_id=$conn->insert_id;
                            for ($i=0;$i<$count;$i++){
                                add_tel_emp_db($telemp[$i],$insert_id);
                            }
                        }                        
                        display_msg('Сотрудник успешно добавлен');
                        return TRUE;
                    }
                    break;
                case UPD_EMP:
                    if (upd_emp($id,$name,$dep, $comment,$post, $err)){
                        edit_element_select($id, get_emp1_tel($id), $telemp, 'del_tel_emp_db', 'add_tel_emp_db','emp');
                        display_msg('Сотрудник успешно обновлен');
                        return TRUE;
                    }
                    break;                    
                default:
                    $err='Неверная опция';
                    break;
            }
        }
        display_error($err);
    } else display_form_add_emp($_POST['action'].'form');
}

function get_all_org() {
    $query="select * from `org` order by name asc";
    return get_data_from_table($query);
}

function get_all_emp() {
    $query="select * from `emp`";
    return get_data_from_table($query);
}

function get_org($id) {
    $query="select * from `org` where id=".$id;
    return get_data_from_table($query);
}

function get_post($id) {
    $query="select * from `post` where id=".$id;
    return get_data_from_table($query);
}

function get_dep($id) {
    $query="select * from `dep` where id=".$id;
    return get_data_from_table($query);
}

function get_emp($id) {
    $query="select * from `emp` where id=".$id;
    return get_data_from_table($query);
}

function get_emp_dep($id) {
    $query="select * from `emp` where id_dep=".$id;
    return get_data_from_table($query);
}

function get_all_dep() {
    $query="select id,name from `dep`";
    return get_data_from_table($query);
}

function get_all_post() {
    $query="select id,name from `post`";
    return get_data_from_table($query);
}

function get_dep_and_org() {
    $query="SELECT t1.id
        , CASE (isnull(t2.name))
          WHEN 0 THEN
            concat(t1.name, ' - ', t2.name)
          WHEN 1 THEN
            concat(t1.name, ' - ', 'Без орган.')
          END
        FROM
          `dep` AS t1
        LEFT JOIN `org` AS t2
        ON t1.id_org = t2.id";
    return get_data_from_table($query);
}

function get_tel($id) {
    $query="select id,number,typenumber from `tel` where id=".$id;
    return get_data_from_table($query);
}

function get_dep_org($id_org){
    $query="select * from `dep` where id_org=".$id_org.' order by name asc';
    return get_data_from_table($query);    
}

function get_emp_tel_org($id_dep) {
    $query="SELECT t3.name,t1.number
FROM
  `tel` AS t1
left JOIN `tel_emp` AS t2
ON t1.id = t2.id_tel
left JOIN `emp` AS t3
ON t2.id_emp = t3.id
  left join `dep` as t4
  on t3.id_dep=t4.id
WHERE
  t4.id =".$id_dep.' order by t3.name asc';
    return get_data_from_table($query);
}

/**
 * Для селекта телефонов
 * @param type $id
 * @return type
 */
function get_tel_emp($id) {
    $query="SELECT  t3.id as 'id_emp'
     , t3.name AS 'fio'
FROM
  `tel` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_tel
JOIN `emp` AS t3
ON t2.id_emp = t3.id
WHERE
  t1.id =".$id;
    return get_data_from_table($query);
}

/**
 * для selecta сотрудников
 * @param type $id
 * @return type
 */
function get_emp_tel($id) {
    $query="SELECT  t3.id as 'id_tel'
     , t3.number AS 'tel'
FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
WHERE
  t1.id =".$id;
    return get_data_from_table($query);
}


/**
 * для selecta сотрудников
 * @param type $id
 * @return type
 */
function get_all_tel() {
    $query="SELECT  * from `tel` order by number asc";
    return get_data_from_table($query);
}


function get_emp1_tel($id) {
    $query="SELECT t3.id as 'id_tel'
FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
WHERE
  t1.id =".$id;
    $conn=db_connect();
    $result=$conn->query($query);
    if (!$result || $result->num_rows<1) return FALSE;
    $array=array();
    $i=0;
    while ($row=$result->fetch_row()){
        $array[$i]=$row[0];
        $i++;
    }
        
    return $array;
    
}

function get_ABC($letter=NULL){
    if (is_null($letter)) {
        $query="SELECT substring(upper(`name`), 1, 1) as 'let', count(*) as 'size'
            FROM
              `emp`
            GROUP BY
              substring(upper(`name`), 1, 1)";
    }
    return get_data_from_table_assoc($query);
}

/**
 * Склонение существительных с числительными
 * @param type $number
 * @param type $titles
 * @return string
 */
function declOfNum($number, $titles){
    $cases = array (2, 0, 1, 1, 1, 2);
    return $number . ' ' . $titles[($number % 100 > 4 && $number % 100 < 20) ? 2 : $cases[min($number % 10, 5)] ] . ' ';
}

function add_link_let(&$letter){
    if (!is_array($letter)) return FALSE;
    $size=count($letter);
    $titles[]='сотрудник';
    $titles[]='сотрудника';
    $titles[]='сотрудников';
    for ($i=0;$i<$size;$i++) {
        $letter[$i]['let']='<a href="index.php?let='.$letter[$i]['let'].'" title="'.declOfNum($letter[$i]['size'],$titles).'">'.$letter[$i]['let'].'</a>';
    }
}

function get_emp_tel_w_let($let) {
    $query="SELECT t1.id
     , t1.name AS 'emp'
     , t6.name AS 'post'
     , t5.name as 'org'
     , t3.number
FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
JOIN `dep` AS t4
ON t1.id_dep = t4.id
JOIN `org` AS t5
ON t4.id_org = t5.id
LEFT JOIN `post` AS t6
ON t6.id = t1.id_post

WHERE
  substring(upper(t1.name), 1, 1) = '".$let."'
ORDER BY
  t1.name ASC
, t3.typenumber DESC
, t3.number ASC";
    return get_data_from_table_assoc($query);
}


function get_info_emp_by_id($id_emp){
    $query="SELECT t1.id
     , t1.name AS 'emp'
     , t6.name AS 'post'
     , t5.name as 'org'
     , t4.name as 'dep'
     , t3.number
FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
JOIN `dep` AS t4
ON t1.id_dep = t4.id
JOIN `org` AS t5
ON t4.id_org = t5.id
LEFT JOIN `post` AS t6
ON t6.id = t1.id_post

WHERE
  t1.id='".$id_emp."'
ORDER BY
  t1.name ASC
, t3.typenumber DESC
, t3.number ASC";
    return get_data_from_table_assoc($query);    
}


function get_tel_org_or_dep_query($id_dep,$id_org){
    if ($id_dep==0) $query="
SELECT t3.number AS 'number'
     , count(*) AS 'count'

FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
JOIN `dep` AS t4
ON t1.id_dep = t4.id
JOIN `org` AS t5
ON t4.id_org = t5.id
WHERE
  t5.id = ".$id_org."
      and substring(t3.number,1,2)=\"28\"
GROUP BY
  t3.number
ORDER BY
  count(*) DESC
LIMIT
  1";
    else if($id_org==0) return FALSE;
    else $query="SELECT t3.number AS 'number'
     , count(*) AS 'count'

FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
JOIN `dep` AS t4
ON t1.id_dep = t4.id
JOIN `org` AS t5
ON t4.id_org = t5.id
WHERE
  t5.id = ".$id_org."
      and t4.id= ".$id_dep."
      and substring(t3.number,1,2)=\"28\"
GROUP BY
  t3.number
ORDER BY
  count(*) DESC
LIMIT
  1";
    return get_data_from_table_assoc($query);
}

function get_tel_org_or_dep($id_dep,$id_org) {
    $array= get_tel_org_or_dep_query($id_dep, $id_org);
    if (is_array($array)) return $array[0]['number'];
}


function get_emp_tel_w_dep($id_dep,$id_org,$let=NULL){
    if (!is_null($let)) return get_emp_tel_w_let($let);
    if ($id_dep==0) 
        if ($id_org==0)
            $query="select t1.id, t1.name as 'emp',t6.name as 'post',t3.number from `emp` as t1 
            join `tel_emp` as t2  on t1.id=t2.id_emp
            join `tel` as t3 on t2.id_tel=t3.id
            join `dep` as t4 on t1.id_dep=t4.id
            join `org` as t5 on t4.id_org=t5.id
            left join `post` as t6 on t6.id=t1.id_post
            order by t1.name asc, t3.typenumber desc, t3.number asc";
        else 
        $query="select t1.id, t4.name as 'dep',t6.name as 'post',t1.name,t3.number from `emp` as t1 
        join `tel_emp` as t2  on t1.id=t2.id_emp
        join `tel` as t3 on t2.id_tel=t3.id
        join `dep` as t4 on t1.id_dep=t4.id
        join `org` as t5 on t4.id_org=t5.id
        left join `post` as t6 on t6.id=t1.id_post
        where t5.id=".$id_org." order by t4.name asc, t1.name asc, t3.typenumber desc, t3.number asc";
    else
    $query="select t1.id, t1.name as 'emp',t4.name as 'post',t3.number from `emp` as t1 
        join `tel_emp` as t2  on t1.id=t2.id_emp
        join `tel` as t3 on t2.id_tel=t3.id
        left join `post` as t4 on t4.id=t1.id_post
        where t1.id_dep=".$id_dep." order by  t1.name asc, t3.typenumber desc, t3.number asc";
    return get_data_from_table_assoc($query);
}


function get_tel_emp1($id) {
    $query="SELECT  t3.id as 'id_emp'
FROM
  `tel` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_tel
JOIN `emp` AS t3
ON t2.id_emp = t3.id
WHERE
  t1.id =".$id;
    $conn=db_connect();
    $result=$conn->query($query);
    if (!$result || $result->num_rows<1) return FALSE;
    $array=array();
    $i=0;
    while ($row=$result->fetch_row()){
        $array[$i]=$row[0];
        $i++;
    }
        
    return $array;
    
}

/**
 * Функция возвращает 2 мерный массив, состоящий из мно-ва id и ФИО+Подразделение+Организация
 * @return type
 */
function get_emp_dep_org() {
    $query="SELECT t3.id

     , CASE isnull(t4.name)
       WHEN 0 THEN
         case isnull(t5.name) 
          when 0 then concat(t3.name, ' (',t4.name,',',t5.name,')')
          when 1 then concat(t3.name, ' (',t4.name,', Без орган.)')
         end
       WHEN 1 THEN
         case isnull(t5.name) 
          when 0 then concat(t3.name, ' (Без подразд.,',t5.name,')')
          when 1 then concat(t3.name, ' (Без подразд., Без орган.)')
         end
       END AS 'str'
FROM
  `emp` AS t3

LEFT JOIN `dep` AS t4
ON t3.id_dep = t4.id
LEFT JOIN `org` AS t5
ON t4.id_org = t5.id
";
    return get_data_from_table($query);
}

function store_org($name=NULL){
    $action=$_POST['action'];
    if (isset($_POST['name']) && !empty($_POST['name']) || isset($name)) {
        $name=$_POST['name'];
        $id=$_POST['id'];
        $err='';
        if (valid_org($name,$err)) {
            switch ($action) {
                case ADD_ALL:
                case ADD_ORG:
                    if (add_org($name,$err)){
                        display_msg('организация успешно добавлена');
                        return TRUE;
                    }
                    break;
                case UPD_ORG:
                    if (upd_org($id,$name,$err)){
                        display_msg('организация успешно обновлена');
                        return TRUE;
                    }                    
                    break;
                default:
                    $err='Неверная опция';
                    break;
            }
        }
        display_error($err);
    } else display_form_add_org ($action.'form');
}


function store_dep($name=NULL,$org=NULL) {
    $action=$_POST['action'];
    if (isset($_POST['name']) && !empty($_POST['name']) || isset($name)) {
        $name=$_POST['name'];
        $id=$_POST['id'];
        if (isset($_REQUEST['org'])) {
            $org=$_REQUEST['org'];
        } else $org=NULL;
        $err='';
        if (valid_dep($name,$err) && check_select($org,'org',$err)) {
            switch ($action) {
                case ADD_ALL:
                case ADD_DEP:
                    if (add_dep($name,$org[0],$err)){
                        display_msg('Подразделение успешно добавлено');
                        return TRUE;
                    }
                    break;
                case UPD_DEP:
                    if (upd_dep($id,$name,$org,$err)){
                        display_msg('Подразделение успешно обновлено');
                        return TRUE;
                    }
                    break;                    
                default:
                    $err='Неверная опция';
                    break;
            }
        }
        display_error($err);        
    } else display_form_add_dep ($action.'form');
} 

function store_tel() {
    $action=$_POST['action'];
    if (isset($_POST['name']) && !empty($_POST['name']) || isset($name)) {
        $number=$_POST['name'];
        $id=$_POST['id'];
        if (isset($_REQUEST['typetel'])) {
            $typetel=$_REQUEST['typetel'];
        } else $typetel=NULL;
        if (isset($_REQUEST['telemp'])) {
            $telemp=$_REQUEST['telemp'];
        } else $telemp=NULL;        
        $err='';
        if (valid_tel($number,$typetel,$err)) {
            switch ($action) {
                case ADD_ALL:
                case ADD_TEL:
                    if (add_tel($number,$typetel,$err)){
                        if (!is_null($telemp)) {
                            $count=count($telemp);
                            $conn=db_connect();
                            $insert_id=$conn->insert_id;
                            for ($i=0;$i<$count;$i++){
                                add_tel_emp_db($insert_id, $telemp[$i]);
                            }
                        }
                        display_msg('Телефон успешно добавлен');
                        return TRUE;
                    }
                    break;
                case UPD_TEL:
                    if (upd_tel($id,$number,$typetel,$err)){
                        edit_element_select($id, get_tel_emp1($id), $telemp, 'del_tel_emp_db', 'add_tel_emp_db','tel');
                        display_msg('Телефон успешно обновлен');
                        return TRUE;
                    }
                    break;                    
                default:
                    $err='Неверная опция';
                    break;
            }
        }
        display_error($err);        
    } else display_form_tel($action.'form');    
}





function store_tel_m() {
    $action=$_POST['action'];
    $number=$_POST['name'];
    $id=$_POST['id'];
    if (isset($_REQUEST['typetel'])) {
        $typetel=$_REQUEST['typetel'];
    } else $typetel=NULL;
    if (isset($_REQUEST['telemp'])) {
        $telemp=$_REQUEST['telemp'];
    } else $telemp=NULL;        
    $err='';
    if (valid_tel($number,$typetel,$err)) {
        switch ($action) {
            case ADD_ALL:
            case ADD_TEL:
                if (add_tel($number,$typetel,$err)){
                    if (!is_null($telemp)) {
                        $count=count($telemp);
                        $conn=db_connect();
                        $insert_id=$conn->insert_id;
                        for ($i=0;$i<$count;$i++){
                            add_tel_emp_db($insert_id, $telemp[$i]);
                        }
                    }
                    display_msg('Телефон <strong>'.$number.'</strong> успешно добавлен');
                    display_form_tel($action.'form');

                }
                break;
            case UPD_TEL:
                if (upd_tel($id,$number,$typetel,$err)){
                    edit_element_select($id, get_tel_emp1($id), $telemp, 'del_tel_emp_db', 'add_tel_emp_db','tel');
                    display_msg('Телефон успешно обновлен на номер <strong>'.$number.'</strong>');
                    display_tel();
                }
                break;                    
            default:
                $err='Неверная опция (сохранение тел.)';
                break;
        }
    }
    if (!empty($err)) { 
        display_error($err); 
        return FALSE; 
    }
    return TRUE;
}







function store_post(){
    $action=$_POST['action'];
    if (isset($_POST['name']) && !empty($_POST['name']) || isset($name)) {
        $name=$_POST['name'];
        $id=$_POST['id'];
        $err='';
        if (valid_post($name,$err)) {
            switch ($action) {
                //case ADD_ALL:
                case ADD_POST:
                    if (add_post($name,$err)){
                        display_msg('должность успешно добавлена');
                        return TRUE;
                    }
                    break;
                case UPD_POST:
                    if (upd_post($id,$name,$err)){
                        display_msg('должность успешно обновлена');
                        return TRUE;
                    }                    
                    break;
                default:
                    $err='Неверная опция';
                    break;
            }
        }
        display_error($err);
    } else display_form_post($action.'form');    
}

function store_all(){
    //orgi - организация из input
    //orgs - организация из select
/*    $action=$_REQUEST['action'];
    $orgi=$_REQUEST['orgi'];
    $depi=$_REQUEST['depi'];
    $fioi=$_REQUEST['fioi'];
    $teli=$_REQUEST['teli'];
    $orgs=$_REQUEST['org'];
    $deps=$_REQUEST['dep'];
    $fios=$_REQUEST['fio'];
    $tels=$_REQUEST['tel'];
    $err='';
    $xorg1=NULL;
    $conn=db_connect();
    $varN=NULL;
    $typetel=1;
    
    if (!empty($orgi)) {
        if (valid_org($orgi, $err)) {
            add_org($orgi, $err);
            $id_org=(int)$conn->insert_id;
        }
    } else { //если не заполнено текстовое поле Организации
        if (!empty($orgs)) {
            $id_org=(int)$orgs[0];
        } else {
            echo 'Не заполнена, или не выбрана организация<br />';
        }
    }
    if (!isset($id_org) || empty($id_org)) $id_org=NULL;
    
    if (!empty($depi)) {
        if (valid_dep($depi, $err)) {
            add_dep($depi, $id_org, $err);
            $id_dep=(int)$conn->insert_id;
        }
    } else {
        if (!empty($deps)) {
            $id_dep=(int)$deps[0];
        } else {
            echo 'Не заполнено, или не выбрано подразделение<br />';
        }
    }
    if (!isset($id_dep) || empty($id_dep)) $id_dep=NULL;


    if (!empty($fioi)) {
        if (valid_emp($fioi, $varN, $err)) {
            add_emp($fioi, $id_dep, $varN, $err);
            $id_emp=(int)$conn->insert_id;
        }
    } else {
        if (!empty($fios)) {
            $id_emp=(int)$deps[0];
        } else {
            echo 'Не заполнен, или не выбран сотрудник<br />';
        }
    }
    if (!isset($id_emp) || empty($id_emp)) $id_emp=NULL;

    if (!empty($teli)) {
        if (valid_tel($teli, $typetel, $err)) {
            add_tel($teli, $typetel, $err);
            $id_tel=(int)$conn->insert_id;
        }
    } else {
        if (!empty($tels)) {
            $id_tel=(int)$tels[0];
        } else {
            echo 'Не заполнен, или не выбран телефон<br />';
        }
    }
    if (!isset($id_tel) || empty($id_tel)) $id_tel=NULL;
    if (!is_null($id_emp) && !is_null($id_tel)) add_tel_emp_db($id_tel, $id_emp); */   
}


abstract class Command {
    abstract function excecute(CommandContext $context);
};

class CommandContext {
    public  $request=array();
    function __construct() {
        $this->request=$_REQUEST;
    }
    public function addParam($key,$value) {
        $this->request[$key]=$value;
    }
    public function getParam($key) {
        if (isset($this->request[$key])) return $this->request[$key];
        else return NULL;
    }
};

?>